﻿Option Explicit On
Imports System.IO
Imports System
Imports System.Data
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports BusinessRules.OAWorkflowXYDB
Imports ICSharpCode.SharpZipLib

Public Class ImplOAContractStamp
    Implements IFlowTools

    '定义全局数据库连接对象
    Private conn As SqlConnection

    '定义事务
    Private ts As SqlTransaction

    Private project As Project

    Private OAWorkflowXYDB As OAWorkflowXYDB.WorkflowServiceForXYDB
    Private webserviceCgmisForOA As WebserviceCgmisForOA.ServiceOA

    Private commonquery As CommonQuery

    Public Sub New(ByVal DbConnection As SqlConnection, ByRef trans As SqlTransaction)
        MyBase.New()
        conn = DbConnection


        '打开数据库连接
        If conn.State = ConnectionState.Closed Then
            conn.Open()
        End If

        '引用外部事务
        ts = trans

        project = New Project(conn, ts)

        OAWorkflowXYDB = New OAWorkflowXYDB.WorkflowServiceForXYDB()
        webserviceCgmisForOA = New WebserviceCgmisForOA.ServiceOA()
        commonquery = New CommonQuery(conn, ts)

    End Sub

    Public Function UseFlowTools(ByVal workFlowID As String, ByVal projectID As String, ByVal taskID As String, ByVal finishedFlag As String, ByVal userID As String) Implements IFlowTools.UseFlowTools

        Dim strSql As String
        Dim strSql1 As String
        Dim dsTemp As DataSet
        Dim dsTemp1 As DataSet

        '主表
        strSql = "select 企业名称,处理A角 from queryProjectInfoForStatistics_Chinese where 项目编码='" + projectID + "'"
        strSql1 = "select p.sign_date, p.remark, p.create_person,p.create_date, p.sign_sum, b.bank_name, bb.branch_bank_name from project_signature as p join bank as b on p.bank = b.bank_code join bank_branch as bb on p.bank = bb.bank_code and p.bank_branch = bb.branch_bank_code where project_code='" + projectID + "'"

        dsTemp = commonquery.GetCommonQueryInfo(strSql)
        dsTemp1 = commonquery.GetCommonQueryInfo(strSql1)


        '企业名称
        Dim strCorporationName As String = dsTemp.Tables(0).Rows(0).Item("企业名称")

        'A角
        Dim strManagerA As String = IIf(IsDBNull(dsTemp.Tables(0).Rows(0).Item("处理A角")), 0, dsTemp.Tables(0).Rows(0).Item("处理A角"))

        '签约日期
        Dim dateOfContract As Date = IIf(IsDBNull(dsTemp1.Tables(0).Rows(0).Item("sign_date")), "", dsTemp1.Tables(0).Rows(0).Item("sign_date"))
        Dim strDateOfContract As String = dateOfContract.ToString("yyyy-MM-dd")

        '签约金额
        Dim strContractSum As String = IIf(IsDBNull(dsTemp1.Tables(0).Rows(0).Item("sign_sum")), "0", dsTemp1.Tables(0).Rows(0).Item("sign_sum"))

        '签约角色
        Dim strManagerOfContract As String = IIf(IsDBNull(dsTemp1.Tables(0).Rows(0).Item("create_person")), 0, dsTemp1.Tables(0).Rows(0).Item("create_person"))

        '签约银行
        Dim bankOfContract As String = IIf(IsDBNull(dsTemp1.Tables(0).Rows(0).Item("bank_name")), "", dsTemp1.Tables(0).Rows(0).Item("bank_name"))

        '签约支行
        Dim bankBranchOfContract As String = IIf(IsDBNull(dsTemp1.Tables(0).Rows(0).Item("branch_bank_name")), "", dsTemp1.Tables(0).Rows(0).Item("branch_bank_name"))

        '备注
        Dim remark As String = IIf(IsDBNull(dsTemp1.Tables(0).Rows(0).Item("remark")), "", dsTemp1.Tables(0).Rows(0).Item("remark"))


        '把签约角色作为申请人 ,获取签约角色的OA对应ID
        strSql = "select id,departmentid from HrmResource where lastname='" + strManagerOfContract + "'"
        dsTemp = webserviceCgmisForOA.GetCommonQueryInfoForOA(strSql)
        Dim iStaffID As Integer = dsTemp.Tables(0).Rows(0).Item("id")
        Dim iDepID As Integer = dsTemp.Tables(0).Rows(0).Item("departmentid")

        '把A角作为申请人 ,获取A角的OA对应ID


        strSql = "select id,departmentid from HrmResource where lastname='" + strManagerA + "'"
        dsTemp = webserviceCgmisForOA.GetCommonQueryInfoForOA(strSql)
        Dim iStaffAID As Integer = dsTemp.Tables(0).Rows(0).Item("id")

        '创建主表字段
        Dim wrti(10) As WorkflowRequestTableField

        '申请人
        wrti(0) = New WorkflowRequestTableField()
        wrti(0).fieldName = "fawjl"
        wrti(0).fieldValue = iStaffID
        wrti(0).view = True
        wrti(0).edit = True
        wrti(0).viewSpecified = True
        wrti(0).editSpecified = True

        '申请部门
        wrti(1) = New WorkflowRequestTableField()
        wrti(1).fieldName = "dept"
        wrti(1).fieldValue = iDepID
        wrti(1).view = True
        wrti(1).edit = True
        wrti(1).viewSpecified = True
        wrti(1).editSpecified = True


        '创建日期
        wrti(2) = New WorkflowRequestTableField()
        wrti(2).fieldName = "create_date"
        wrti(2).fieldValue = Now.ToString("yyyy-MM-dd")
        wrti(2).view = True
        wrti(2).edit = True
        wrti(2).viewSpecified = True
        wrti(2).editSpecified = True



        '项目编号
        wrti(3) = New WorkflowRequestTableField()
        wrti(3).fieldName = "xiangmbh"
        wrti(3).fieldValue = projectID
        wrti(3).view = True
        wrti(3).edit = True
        wrti(3).viewSpecified = True
        wrti(3).editSpecified = True


        '企业名称
        wrti(4) = New WorkflowRequestTableField()
        wrti(4).fieldName = "qiymc"
        wrti(4).fieldValue = strCorporationName
        wrti(4).view = True
        wrti(4).edit = True
        wrti(4).viewSpecified = True
        wrti(4).editSpecified = True


        '签约金额
        wrti(5) = New WorkflowRequestTableField()
        wrti(5).fieldName = "qianyje"
        wrti(5).fieldValue = strContractSum
        wrti(5).view = True
        wrti(5).edit = True
        wrti(5).viewSpecified = True
        wrti(5).editSpecified = True

        '签约银行
        wrti(6) = New WorkflowRequestTableField()
        wrti(6).fieldName = "qianyyh"
        wrti(6).fieldValue = bankOfContract
        wrti(6).view = True
        wrti(6).edit = True
        wrti(6).viewSpecified = True
        wrti(6).editSpecified = True

        '签约支行
        wrti(7) = New WorkflowRequestTableField()
        wrti(7).fieldName = "qianyzh"
        wrti(7).fieldValue = bankBranchOfContract
        wrti(7).view = True
        wrti(7).edit = True
        wrti(7).viewSpecified = True
        wrti(7).editSpecified = True


        '备注
        wrti(8) = New WorkflowRequestTableField()
        wrti(8).fieldName = "beiz"
        wrti(8).fieldValue = remark
        wrti(8).view = True
        wrti(8).edit = True
        wrti(8).viewSpecified = True
        wrti(8).editSpecified = True


        '签约日期
        wrti(9) = New WorkflowRequestTableField()
        wrti(9).fieldName = "qianyrq"
        wrti(9).fieldValue = strDateOfContract
        wrti(9).view = True
        wrti(9).edit = True
        wrti(9).viewSpecified = True
        wrti(9).editSpecified = True

        'A角
        wrti(10) = New WorkflowRequestTableField()
        wrti(10).fieldName = "juea"
        wrti(10).fieldValue = iStaffAID
        wrti(10).view = True
        wrti(10).edit = True
        wrti(10).viewSpecified = True
        wrti(10).editSpecified = True


        '主字段只有一行数据
        Dim wrtri(0) As WorkflowRequestTableRecord
        wrtri(0) = New WorkflowRequestTableRecord
        wrtri(0).workflowRequestTableFields = wrti

        Dim wmi As New WorkflowMainTableInfo()
        wmi.requestRecords = wrtri



        '明细表
        strSql = "select title,author,max(create_date) as create_date from project_files where item_type='43' and project_code='" + projectID + "'" + "group by title,author"


        dsTemp = commonquery.GetCommonQueryInfo(strSql)




        '合同数目
        Dim count As Integer = dsTemp.Tables(0).Rows.Count



        Dim wdti(0) As WorkflowDetailTableInfo
        wdti(0) = New WorkflowDetailTableInfo()
        Dim wrtrid(count - 1) As WorkflowRequestTableRecord

        Dim I As Integer
        For I = 0 To count - 1
            '合同名称
            Dim contractName As String = IIf(IsDBNull(dsTemp.Tables(0).Rows(I).Item("title")), "", dsTemp.Tables(0).Rows(I).Item("title"))

            '作者
            Dim author As String = IIf(IsDBNull(dsTemp.Tables(0).Rows(I).Item("author")), 0, dsTemp.Tables(0).Rows(I).Item("author"))

            '创建日期
            Dim createContractDate As Date = IIf(IsDBNull(dsTemp.Tables(0).Rows(I).Item("create_date")), "", dsTemp.Tables(0).Rows(I).Item("create_date"))
            Dim strCreateContractDate As String = dateOfContract.ToString("yyyy-MM-dd")

            '把签约角色作为申请人 ,获取签约角色的OA对应ID
            strSql = "select id from HrmResource where lastname='" + author + "'"
            dsTemp1 = webserviceCgmisForOA.GetCommonQueryInfoForOA(strSql)
            Dim nameID As Integer = dsTemp1.Tables(0).Rows(0).Item("id")

            Dim wrtid(2) As WorkflowRequestTableField

            '合同名称
            wrtid(0) = New WorkflowRequestTableField()
            wrtid(0).fieldName = "hetmc"
            wrtid(0).fieldValue = contractName
            wrtid(0).view = True
            wrtid(0).edit = True
            wrtid(0).viewSpecified = True
            wrtid(0).editSpecified = True

            '作者
            wrtid(1) = New WorkflowRequestTableField()
            wrtid(1).fieldName = "author"
            wrtid(1).fieldValue = nameID
            wrtid(1).view = True
            wrtid(1).edit = True
            wrtid(1).viewSpecified = True
            wrtid(1).editSpecified = True


            '创建日期
            wrtid(2) = New WorkflowRequestTableField()
            wrtid(2).fieldName = "create_date"
            wrtid(2).fieldValue = "2014-09-12"
            wrtid(2).view = True
            wrtid(2).edit = True
            wrtid(2).viewSpecified = True
            wrtid(2).editSpecified = True

            wrtrid(I) = New WorkflowRequestTableRecord()
            wrtrid(I).workflowRequestTableFields = wrtid

        Next

        wdti(0).workflowRequestTableRecords = wrtrid


        Dim wbi As New WorkflowBaseInfo()
        wbi.workflowId = "93"
        Dim wri As New WorkflowRequestInfo()
        wri.creatorId = "56"
        wri.requestName = strCorporationName + "合同盖章流程"
        wri.workflowMainTableInfo = wmi
        wri.workflowBaseInfo = wbi
        wri.workflowDetailTableInfos = wdti


        Dim result As String
        result = OAWorkflowXYDB.doCreateWorkflowRequest(wri, 56)




    End Function

End Class
